﻿using Ayatta.Contract.Component;
using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Linq;
using Ayatta.Contract;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class BaseService : ServiceBase, IBaseService
    {
        //#region Location

        //public IList<Location> GetLocations()
        //{
        //    var builder = new SqlBuilder()
        //        .Field("*")
        //        .Table(Database.Base.Table.Location)
        //        .OrderBy("Id ASC");

        //    var template = builder.AddTemplate(SqlTemplate.Select);
        //    using (var connection = GetConnection(ConnectionName.Base))
        //    {
        //        return Query<Location>(connection, template).ToList();
        //    }
        //}

        //#endregion

        #region Area

        public IList<Area> GetAreas()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.Area)
                .OrderBy("Id ASC");

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<Area>(connection, template).ToList();
            }
        }

        #endregion

        #region Logistics.Company

        public IList<LogisticsCompany> GetLogisticsCompanies()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.LogisticsCompany);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<LogisticsCompany>(connection, template).ToList();
            }
        }

        #endregion

        #region FAQ

        public bool CreateFaq(Faq o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.Faq)
                .Field(
                    "([Title],[Content],[Category],[DisplayOrder],[ShowOnMenu],[CreatedBy],[CreatedOn])VALUES(@Title,@Content,@Category,@DisplayOrder,@ShowOnMenu,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new { o.Title, o.Content, o.Category, o.DisplayOrder, o.ShowOnMenu, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateFaq(Faq o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.Faq)
                .Field("[Title]=@Title", new { o.Title })
                .Field("[Content]=@Content", new { o.Content })
                .Field("[Category]=@Category", new { o.Category })
                .Field("[DisplayOrder]=@DisplayOrder", new { o.DisplayOrder })
                .Field("[ShowOnMenu]=@ShowOnMenu", new { o.ShowOnMenu })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteFaq(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.Faq)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Faq GetFaq(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.Faq)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<Faq>(connection, template).FirstOrDefault();
            }
        }

        public PagedList<Faq> GetPagedFaqs(int index = 1, int size = 20, string title = null,
                                                           byte? category = null, bool? showOnMenu = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.Faq)
                .OrderBy("Category,DisplayOrder")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (!string.IsNullOrEmpty(title))
            {
                builder.Where("[Title] LIKE @Title", new { title = title + "%" });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category] = @Category", new { category });
            }
            if (showOnMenu.HasValue)
            {
                builder.Where("[ShowOnMenu]=@ShowOnMenu", new { showOnMenu });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.PagedQuery<Faq>(template);
            }
        }

        #endregion

        #region EmailAccount

        public bool CreateEmailAccount(EmailAccount o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Field(
                    "([Category],[Email],[DisplayName],[Host],[Port],[Username],[Password],[EnableSsl],[UseDefaultCredentials],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@Email,@DisplayName,@Host,@Port,@Username,@Password,@EnableSsl,@UseDefaultCredentials,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {
                        o.Category,
                        o.Email,
                        o.DisplayName,
                        o.Host,
                        o.Port,
                        o.Username,
                        o.Password,
                        o.EnableSsl,
                        o.UseDefaultCredentials,
                        o.Status,
                        o.CreatedBy,
                        o.CreatedOn
                    });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateEmailAccount(EmailAccount o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Field("[Category]=@Category", new { o.Category })
                .Field("[Email]=@Email", new { o.Email })
                .Field("[DisplayName]=@DisplayName", new { o.DisplayName })
                .Field("[Host]=@Host", new { o.Host })
                .Field("[Port]=@Port", new { o.Port })
                .Field("[Username]=@Username", new { o.Username })
                .Field("[Password]=@Password", new { o.Password })
                .Field("[EnableSsl]=@EnableSsl", new { o.EnableSsl })
                .Field("[UseDefaultCredentials]=@UseDefaultCredentials", new { o.UseDefaultCredentials })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteEmailAccount(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public EmailAccount GetEmailAccount(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailAccount)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);

            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailAccount>(connection, template).FirstOrDefault();
            }
        }

        public IList<EmailAccount> GetAllEmailAccount()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailAccount);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailAccount>(connection, template).ToList();
            }
        }

        public PagedList<EmailAccount> GetPagedEmailAccount(int index = 1, int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.EmailAccount)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.PagedQuery<EmailAccount>(template);
            }
        }  

        #endregion

        #region EmailTemplate

        public bool CreateEmailTemplate(EmailTemplate o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Field(
                    "([Name],[BccEmailAddresses],[Subject],[Body],[Description],[CreatedBy],[CreatedOn])VALUES(@Name,@BccEmailAddresses,@Subject,@Body,@Description,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new { o.Name, o.BccEmailAddresses, o.Subject, o.Body, o.Description, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateEmailTemplate(EmailTemplate o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Field("[Name]=@Name", new { o.Name })
                .Field("[BccEmailAddresses]=@BccEmailAddresses", new { o.BccEmailAddresses })
                .Field("[Subject]=@Subject", new { o.Subject })
                .Field("[Body]=@Body", new { o.Body })
                .Field("[Description]=@Description", new { o.Description })
                .Where("[Version]=@Version", new { o.Version })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteEmailTemplate(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public EmailTemplate GetEmailTemplate(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailTemplate)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailTemplate>(connection, template).FirstOrDefault();
            }
        }

        public IList<EmailTemplate> GetAllEmailTemplate()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailTemplate);
            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailTemplate>(connection, template).ToList();
            }
        }

        public PagedList<EmailTemplate> GetPagedEmailTemplate(int index = 1, int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.EmailTemplate)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.PagedQuery<EmailTemplate>(template);
            }
        }

        #endregion

        #region Slide
        public bool CreateSlideCategory(SlideCategory o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Base.Table.SlideCategory)
            .Field("([Category],[Title],[Height],[Width],[Thumb],[ThumbH],[ThumbW],[Description],[Priority],[Extra],[Status])VALUES(@Category,@Title,@Height,@Width,@Thumb,@ThumbH,@ThumbW,@Description,@Priority,@Extra,@Status)")
            .AddParameters(new { o.Category, o.Title, o.Height, o.Width, o.Thumb, o.ThumbH, o.ThumbW, o.Description, o.Priority, o.Extra, o.Status });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }
        public bool UpdateSlideCategory(SlideCategory o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Base.Table.SlideCategory)
            .Field("[Category]=@Category", new { o.Category })
            .Field("[Title]=@Title", new { o.Title })
            .Field("[Height]=@Height", new { o.Height })
            .Field("[Width]=@Width", new { o.Width })
            .Field("[Thumb]=@Thumb", new { o.Thumb })
            .Field("[ThumbH]=@ThumbH", new { o.ThumbH })
            .Field("[ThumbW]=@ThumbW", new { o.ThumbW })
            .Field("[Description]=@Description", new { o.Description })
            .Field("[Priority]=@Priority", new { o.Priority })
            .Field("[Extra]=@Extra", new { o.Extra })
            .Field("[Status]=@Status", new { o.Status })
            .Where("[Id] = @Id", new { o.Id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }
        public SlideCategory GetSlideCategory(int id)
        {
            var builder = new SqlBuilder()
               .Field("*")
               .Table(Database.Base.Table.SlideCategory)
               .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<SlideCategory>(connection, template).FirstOrDefault();
            }
        }

        public IList<SlideCategory> GetSlideCategories()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.SlideCategory);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<SlideCategory>(connection, template).ToList();
            }
        }

        public IList<SlideCategory> GetAvailableSlideCategories()
        {
            var tpl = string.Format("SELECT * FROM {0} WHERE [Status]=1;SELECT * FROM {1} WHERE [Status]=1 AND ([StartTime]>=GETDATE() AND GETDATE()<=[EndTime]);", Database.Base.Table.SlideCategory, Database.Base.Table.Slide);

            var template = new SqlBuilder().AddTemplate(tpl);

            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.QueryMultiple(template).Map<SlideCategory, Slide, int>(a => a.Id, b => b.CategoryId, (a, b) => { a.Slides = b.ToList(); }).ToList();
            }
        }

        public PagedList<SlideCategory> GetPagedSlideCategories(int index = 1, int size = 20, string title = null, int? category = null, bool? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.SlideCategory)
                .OrderBy("Id Desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (!string.IsNullOrEmpty(title))
            {
                builder.Where("[Title] LIKE @Title", new { title = title + "%" });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category] = @Category", new { category });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.PagedQuery<SlideCategory>(template);
            }
        }

        public bool CreateSlide(Slide o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Base.Table.Slide)
            .Field("([CategoryId],[Title],[Link],[Image],[Thumb],[Description],[Priority],[Condition],[StartTime],[EndTime],[Extra],[Status])VALUES(@CategoryId,@Title,@Link,@Image,@Thumb,@Description,@Priority,@Condition,@StartTime,@EndTime,@Extra,@Status)")
            .AddParameters(new { o.CategoryId, o.Title, o.Link, o.Image, o.Thumb, o.Description, o.Priority, o.Condition, o.StartTime, o.EndTime, o.Extra, o.Status });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }
        public bool UpdateSlide(Slide o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Base.Table.Slide)
            .Field("[CategoryId]=@CategoryId", new { o.CategoryId })
            .Field("[Title]=@Title", new { o.Title })
            .Field("[Link]=@Link", new { o.Link })
            .Field("[Image]=@Image", new { o.Image })
            .Field("[Thumb]=@Thumb", new { o.Thumb })
            .Field("[Description]=@Description", new { o.Description })
            .Field("[Priority]=@Priority", new { o.Priority })
            .Field("[Condition]=@Condition", new { o.Condition })
            .Field("[StartTime]=@StartTime", new { o.StartTime })
            .Field("[EndTime]=@EndTime", new { o.EndTime })
            .Field("[Extra]=@Extra", new { o.Extra })
            .Field("[Status]=@Status", new { o.Status })
            .Where("[Id] = @Id", new { o.Id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteSlide(int id)
        {
            var builder = new SqlBuilder()
            .Table(Database.Base.Table.Slide)
            .Where("[Id] = @Id", new { id });
            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Slide GetSlide(int id, bool includeCategory = false)
        {
            if (includeCategory)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(Database.Base.Table.Slide + " a")
                    .InnerJoin(Database.Base.Table.SlideCategory + " b on a.CategoryId=b.Id")
                    .Where("a.[Id] = @Id", new { id });

                var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
                using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
                {
                    return connection.Query<Slide, SlideCategory, Slide>(template, (a, b) => { a.Category = b; return a; }).FirstOrDefault();
                }
            }
            else
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(Database.Base.Table.Slide)
                    .Where("[Id] = @Id", new { id });

                var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
                using (var connection = GetConnection(ConnectionName.Base))
                {
                    return Query<Slide>(connection, template).FirstOrDefault();
                }
            }
        }

        public PagedList<Slide> GetPagedSlides(int index = 1, int size = 20, int categoryId = 0, string title = null, bool? status = null, DateTime? startTime = null, DateTime? endTime = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.Slide)
                .OrderBy("Id Desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (!string.IsNullOrEmpty(title))
            {
                builder.Where("[Title] LIKE @Title", new { title = title + "%" });
            }
            if (categoryId > 0)
            {
                builder.Where("[CategoryId] = @CategoryId", new { categoryId });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            if (startTime.HasValue)
            {
                builder.Where("[StartTime]>=@StartTime", new { startTime });
            }
            if (endTime.HasValue)
            {
                builder.Where("[EndTime]<=@EndTime", new { endTime });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base).EnsureOpen())
            {
                return connection.PagedQuery<Slide>(template);
            }
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSql()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}